package repository

import (
	"context"
	"database/sql"
	"errors"
	"time"

	"gitee.com/hp-huiw/family-expense-api/internal/db"
	dbmodel "gitee.com/hp-huiw/family-expense-api/internal/db/model"
	"gitee.com/hp-huiw/my-go-core/date"
	"gitee.com/hp-huiw/my-go-core/util"
	jet "github.com/go-jet/jet/v2/mysql"
	"github.com/go-jet/jet/v2/qrm"
)

type UserStatus struct {
	ID              string `db:"id"`
	Username        string `db:"username"`
	Enabled         bool   `db:"enabled"`
	AccountExpired  bool   `db:"account_expired"`
	AccountLocked   bool   `db:"account_locked"`
	PasswordExpired bool   `db:"password_expired"`
}

type UserBasicInfo struct {
	ID                    string  `db:"id"`
	Username              string  `db:"username"`
	Enabled               *bool   `db:"enabled"`
	FullName              string  `db:"full_name"`
	AvatarFilename        *string `db:"avatar_filename"`
	IsAccountGroupCreator *bool   `db:"is_account_group_creator"`
}

type UserDetail struct {
	ID                    string    `db:"id"`
	Username              string    `db:"username"`
	Enabled               *bool     `db:"enabled"`
	FullName              string    `db:"full_name"`
	AvatarFilename        *string   `db:"avatar_filename"`
	IsAccountGroupCreator *bool     `db:"is_account_group_creator"`
	AccountGroupID        string    `db:"account_group_id"`
	Description           *string   `db:"description"`
	DateCreated           time.Time `db:"date_created"`
	LastUpdated           time.Time `db:"last_updated"`
}

type UserRepository interface {
	QueryAvatarFilename(ctx context.Context, userID string) (*string, error)
	QueryBasicInfo(ctx context.Context, accountGroupID string, usernames []string) ([]UserBasicInfo, error)
	QueryPassword(ctx context.Context, username string) (string, error)
	QueryStatus(ctx context.Context, username string) (*UserStatus, error)
	QueryDetails(ctx context.Context, accountGroupID string, idOrUsername string) (*UserDetail, error)
	QueryUsersNotInAccountGroup(ctx context.Context, accountGroupID string, keyword string, pageSize int64, pageIndex int64) ([]UserBasicInfo, error)
	QueryAccountGroupUsers(ctx context.Context, accountGroupID string, pageSize int64, pageIndex int64) ([]UserBasicInfo, error)
	Count(ctx context.Context, accountGroupID string) (int64, error)
	ExistsByUsername(ctx context.Context, username string) (bool, error)
	ExistsByUserIDAndAccountGroupID(ctx context.Context, userID string, accountGroupID string) (bool, error)
	UpdateAccountGroup(ctx context.Context, accountGroupID string, userID string) error
	UpdateAvatarFilename(ctx context.Context, id string, avatarFilename string) error
	UpdateUserInfo(ctx context.Context, username string, fullName string, description *string) error
	UpdatePassword(ctx context.Context, accountGroupID string, username string, password string) error
	RestoreAccountGroupByUserIDWithTx(ctx context.Context, tx *sql.Tx, userID string) error
	RestoreAccountGroupByAccountGroupIDWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string) error
	InsertWithTx(ctx context.Context, tx *sql.Tx, model *dbmodel.User) error
	DeleteWithTx(ctx context.Context, tx *sql.Tx, id string) error
}

func NewUserRepository(db *sql.DB) UserRepository {
	return &userRepository{db: db}
}

// QueryAvatarFilename queries user's avatar filename by user id.
func (r *userRepository) QueryAvatarFilename(ctx context.Context, userID string) (*string, error) {
	var query struct {
		AvatarFilename string `db:"avatar_filename"`
	}

	stmt := jet.
		SELECT(
			tbUser.AvatarFilename.AS("avatar_filename"),
		).
		FROM(
			tbUser,
		).
		WHERE(
			tbUser.ID.EQ(jet.String(userID)),
		)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		if errors.Is(err, qrm.ErrNoRows) {
			return nil, nil
		}

		return nil, err
	}

	return &query.AvatarFilename, nil
}

// QueryBasicInfo queries basic info by account id and usernames.
func (r *userRepository) QueryBasicInfo(ctx context.Context, accountGroupID string, usernames []string) ([]UserBasicInfo, error) {
	var query []struct {
		ID                    string  `db:"id"`
		Username              string  `db:"username"`
		Enabled               *bool   `db:"enabled"`
		FullName              string  `db:"full_name"`
		AvatarFilename        *string `db:"avatar_filename"`
		IsAccountGroupCreator *bool   `db:"is_account_group_creator"`
	}

	if len(usernames) > 0 {
		usernamesExpression := db.SliceExpression(usernames)

		stmt := jet.
			SELECT(
				tbUser.ID.AS("id"),
				tbUser.Username.AS("username"),
				tbUser.FullName.AS("full_name"),
				tbUser.AvatarFilename.AS("avatar_filename"),
			).
			FROM(
				tbUser,
			).
			WHERE(
				tbUser.AccountGroupID.EQ(jet.String(accountGroupID)).
					AND(tbUser.Username.IN(usernamesExpression...)),
			)

		if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
			return nil, err
		}
	}

	result := make([]UserBasicInfo, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

func (r *userRepository) QueryPassword(ctx context.Context, username string) (string, error) {
	var query struct {
		Password string `db:"password"`
	}

	stmt := jet.
		SELECT(
			tbUser.Password.AS("password"),
		).
		FROM(
			tbUser,
		).
		WHERE(
			tbUser.Username.EQ(jet.String(username)),
		)

	err := stmt.QueryContext(ctx, r.db, &query)

	if err != nil {
		return "", err
	}

	return query.Password, nil
}

func (r *userRepository) QueryStatus(ctx context.Context, username string) (*UserStatus, error) {
	var query struct {
		ID              string `db:"id"`
		Username        string `db:"username"`
		Enabled         bool   `db:"enabled"`
		AccountExpired  bool   `db:"account_expired"`
		AccountLocked   bool   `db:"account_locked"`
		PasswordExpired bool   `db:"password_expired"`
	}

	stmt := jet.
		SELECT(
			tbUser.ID.AS("id"),
			tbUser.Username.AS("username"),
			tbUser.Enabled.AS("enabled"),
			tbUser.AccountExpired.AS("account_expired"),
			tbUser.AccountLocked.AS("account_locked"),
			tbUser.PasswordExpired.AS("password_expired"),
		).
		FROM(
			tbUser,
		).
		WHERE(
			tbUser.Username.EQ(jet.String(username)),
		)

	err := stmt.QueryContext(ctx, r.db, &query)

	if err != nil {
		return nil, err
	}

	return (*UserStatus)(&query), nil
}

func (r *userRepository) QueryDetails(ctx context.Context, accountGroupID string, idOrUsername string) (*UserDetail, error) {
	var query struct {
		ID                    string    `db:"id"`
		Username              string    `db:"username"`
		Enabled               *bool     `db:"enabled"`
		FullName              string    `db:"full_name"`
		AvatarFilename        *string   `db:"avatar_filename"`
		IsAccountGroupCreator *bool     `db:"is_account_group_creator"`
		AccountGroupID        string    `db:"account_group_id"`
		Description           *string   `db:"description"`
		DateCreated           time.Time `db:"date_created"`
		LastUpdated           time.Time `db:"last_updated"`
	}

	var from jet.ReadableTable
	var condition jet.BoolExpression

	if !util.IsEmptyString(accountGroupID) {
		// This is for query account group user details.
		from = tbUser.
			INNER_JOIN(tbGroupUser, tbUser.ID.EQ(tbGroupUser.UserID).
				AND(tbUser.AccountGroupID.EQ(tbGroupUser.AccountGroupID)))

		condition = jet.AND(
			jet.OR(
				tbUser.ID.EQ(jet.String(idOrUsername)),
				tbUser.Username.EQ(jet.String(idOrUsername)),
			),
			tbGroupUser.AccountGroupID.EQ(jet.String(accountGroupID)),
		)
	} else {
		// It is for authentication usage.
		from = tbUser

		condition = tbUser.ID.EQ(jet.String(idOrUsername)).
			OR(tbUser.Username.EQ(jet.String(idOrUsername)))
	}

	stmt := jet.
		SELECT(
			tbUser.AccountGroupID.AS("account_group_id"),
			tbUser.ID.AS("id"),
			tbUser.Username.AS("username"),
			tbUser.FullName.AS("full_name"),
			tbUser.AvatarFilename.AS("avatar_filename"),
			tbUser.Description.AS("description"),
			tbUser.Enabled.AS("enabled"),
			tbUser.DateCreated.AS("date_created"),
			tbUser.LastUpdated.AS("last_updated"),
		).
		FROM(
			from,
		).
		WHERE(
			condition,
		)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	return (*UserDetail)(&query), nil
}

func (r *userRepository) QueryUsersNotInAccountGroup(ctx context.Context, accountGroupID string, keyword string, pageSize int64, pageIndex int64) ([]UserBasicInfo, error) {
	// We have to exclude these users:
	// - The owner of this account group.
	// - The user has this account group as his/her current account group.
	// - The user has this account group as NOT his/her current account group.
	var query []struct {
		ID                    string  `db:"id"`
		Username              string  `db:"username"`
		Enabled               *bool   `db:"enabled"`
		FullName              string  `db:"full_name"`
		AvatarFilename        *string `db:"avatar_filename"`
		IsAccountGroupCreator *bool   `db:"is_account_group_creator"`
	}

	// Queries all users which belongs to this account group.
	subQuery := jet.
		SELECT(
			tbGroupUser.UserID,
		).
		FROM(
			tbGroupUser,
		).
		WHERE(
			tbGroupUser.AccountGroupID.EQ(jet.String(accountGroupID)),
		)

		// Finds all account group creators with username like and not in the account group.
	stmt := jet.
		SELECT(
			tbUser.ID.AS("id"),
			tbUser.Username.AS("username"),
			tbUser.FullName.AS("full_name"),
			tbUser.AvatarFilename.AS("avatar_filename"),
		).
		FROM(
			tbUser.
				INNER_JOIN(tbGroupUser, tbUser.ID.EQ(tbGroupUser.UserID).
					AND(tbUser.AccountGroupID.EQ(tbGroupUser.AccountGroupID))),
		).
		WHERE(
			tbUser.Username.LIKE(jet.String("%" + keyword + "%")).
				AND(tbUser.ID.NOT_IN(subQuery)).
				AND(tbGroupUser.IsAccountGroupCreator.IS_TRUE()),
		).
		LIMIT(pageSize).
		OFFSET(pageSize * pageIndex)

	err := stmt.QueryContext(ctx, r.db, &query)

	if err != nil {
		return nil, err
	}

	result := make([]UserBasicInfo, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

func (r *userRepository) QueryAccountGroupUsers(ctx context.Context, accountGroupID string, pageSize int64, pageIndex int64) ([]UserBasicInfo, error) {
	var query []struct {
		ID                    string  `db:"id"`
		Username              string  `db:"username"`
		Enabled               *bool   `db:"enabled"`
		FullName              string  `db:"full_name"`
		AvatarFilename        *string `db:"avatar_filename"`
		IsAccountGroupCreator *bool   `db:"is_account_group_creator"`
	}

	stmt := jet.
		SELECT(
			tbUser.ID.AS("id"),
			tbUser.Username.AS("username"),
			tbUser.FullName.AS("full_name"),
			tbUser.AvatarFilename.AS("avatar_filename"),
			tbUser.Enabled.AS("enabled"),
			tbGroupUser.IsAccountGroupCreator.AS("is_account_group_creator"),
		).
		FROM(
			tbUser.
				INNER_JOIN(tbGroupUser, tbUser.ID.EQ(tbGroupUser.UserID).
					AND(tbUser.AccountGroupID.EQ(tbGroupUser.AccountGroupID))),
		).
		WHERE(
			tbUser.AccountGroupID.EQ(jet.String(accountGroupID)),
		).
		ORDER_BY(
			tbUser.Enabled.DESC(),
		).
		LIMIT(pageSize).
		OFFSET(pageSize * pageIndex)

	err := stmt.QueryContext(ctx, r.db, &query)

	if err != nil {
		return nil, err
	}

	result := make([]UserBasicInfo, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

func (r *userRepository) Count(ctx context.Context, accountGroupID string) (int64, error) {
	return db.Count(ctx, r.db, tbUser, tbUser.AccountGroupID.EQ(jet.String(accountGroupID)))
}

func (r *userRepository) ExistsByUsername(ctx context.Context, username string) (bool, error) {
	return db.Exists(ctx, r.db, tbUser, tbUser.Username.EQ(jet.String(username)))
}

func (r *userRepository) ExistsByUserIDAndAccountGroupID(ctx context.Context, userID string, accountGroupID string) (bool, error) {
	condition := tbUser.ID.EQ(jet.String(userID)).
		AND(tbUser.AccountGroupID.EQ(jet.String(accountGroupID)))

	return db.Exists(ctx, r.db, tbUser, condition)
}

func (r *userRepository) UpdateAccountGroup(ctx context.Context, accountGroupID string, userID string) error {
	stmt := tbUser.
		UPDATE().
		SET(
			tbUser.AccountGroupID.SET(jet.String(accountGroupID)),
		).
		WHERE(
			tbUser.ID.EQ(jet.String(userID)),
		)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

func (r *userRepository) UpdateAvatarFilename(ctx context.Context, id string, avatarFilename string) error {
	stmt := tbUser.
		UPDATE().
		SET(
			tbUser.AvatarFilename.SET(jet.String(avatarFilename)),
			tbUser.LastUpdated.SET(jet.DateTimeT(date.GetCurrentDate())),
		).
		WHERE(tbUser.ID.EQ(jet.String(id)))

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

func (r *userRepository) UpdateUserInfo(ctx context.Context, username string, fullName string, description *string) error {
	stmt := tbUser.
		UPDATE().
		SET(
			tbUser.FullName.SET(jet.String(fullName)),
			tbUser.Description.SET(jet.String(*description)),
		).
		WHERE(
			tbUser.Username.EQ(jet.String(username)),
		)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

func (r *userRepository) UpdatePassword(ctx context.Context, accountGroupID string, username string, password string) error {
	stmt := tbUser.
		UPDATE().
		SET(
			tbUser.Password.SET(jet.String(password)),
		).
		WHERE(
			tbUser.Username.EQ(jet.String(username)).
				AND(tbUser.AccountGroupID.EQ(jet.String(accountGroupID))),
		)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

func (*userRepository) RestoreAccountGroupByUserIDWithTx(ctx context.Context, tx *sql.Tx, userID string) error {
	subQuery := jet.
		SELECT(
			tbGroupUser.AccountGroupID,
		).
		FROM(
			tbGroupUser,
		).
		WHERE(
			tbGroupUser.UserID.EQ(jet.String(userID)).
				AND(tbGroupUser.IsAccountGroupCreator.IS_TRUE()),
		)

	stmt := tbUser.
		UPDATE().
		SET(
			tbUser.AccountGroupID.SET(jet.StringExp(subQuery)),
		).
		WHERE(
			tbUser.ID.EQ(jet.String(userID)),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

func (*userRepository) RestoreAccountGroupByAccountGroupIDWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string) error {
	subQuery := jet.
		SELECT(
			tbGroupUser.AccountGroupID,
		).
		FROM(
			tbGroupUser,
		).
		WHERE(
			tbGroupUser.UserID.EQ(tbUser.ID).
				AND(tbGroupUser.IsAccountGroupCreator.IS_TRUE()),
		).
		LIMIT(1)

	stmt := tbUser.
		UPDATE().
		SET(
			tbUser.AccountGroupID.SET(jet.StringExp(subQuery)),
		).
		WHERE(
			tbUser.AccountGroupID.EQ(jet.String(accountGroupID)),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

// InsertWithTx inserts user with transaction.
func (*userRepository) InsertWithTx(ctx context.Context, tx *sql.Tx, model *dbmodel.User) error {
	stmt := tbUser.
		INSERT().
		MODEL(model)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

// DeleteWithTx deletes user by id with transaction.
func (*userRepository) DeleteWithTx(ctx context.Context, tx *sql.Tx, id string) error {
	stmt := tbUser.
		DELETE().
		WHERE(
			tbUser.ID.EQ(jet.String(id)),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

type userRepository struct {
	db *sql.DB
}
